import sqlite3

# 1. 连接数据库（如果文件不存在则自动创建）
# 数据库文件将保存在当前目录，名为 "test_database.db"
conn = sqlite3.connect('test_database.db')

# 2. 创建游标对象（用于执行 SQL 语句）
cursor = conn.cursor()

# 3. 执行 SQL 语句（示例：创建表）
try:
    # 创建一个名为 "users" 的表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            age INTEGER,
            email TEXT UNIQUE
        )
    ''')
    print("表创建成功")
except sqlite3.Error as e:
    print(f"创建表失败：{e}")

# 4. 插入数据
try:
    cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)",
                   ("Alice", 25, "alice@example.com"))
    cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)",
                   ("Bob", 30, "bob@example.com"))
    # 提交事务（插入/更新/删除需要提交）
    conn.commit()
    print("数据插入成功")
except sqlite3.IntegrityError:
    print("邮箱已存在，插入失败")
except sqlite3.Error as e:
    print(f"插入数据失败：{e}")
    # 出错时回滚事务
    conn.rollback()

# 5. 查询数据
try:
    cursor.execute("SELECT * FROM users")
    # 获取所有结果
    all_users = cursor.fetchall()
    print("\n所有用户：")
    for user in all_users:
        print(user)  # 输出格式：(id, name, age, email)

    # 条件查询
    cursor.execute("SELECT name, age FROM users WHERE age > 28")
    print("\n年龄大于28的用户：")
    for user in cursor.fetchall():
        print(user)
except sqlite3.Error as e:
    print(f"查询失败：{e}")

# 6. 更新数据
try:
    cursor.execute("UPDATE users SET age = ? WHERE name = ?", (26, "Alice"))
    conn.commit()
    print("\n数据更新成功")
except sqlite3.Error as e:
    print(f"更新失败：{e}")
    conn.rollback()

# 7. 删除数据
try:
    cursor.execute("DELETE FROM users WHERE name = ?", ("Bob",))
    conn.commit()
    print("数据删除成功")
except sqlite3.Error as e:
    print(f"删除失败：{e}")
    conn.rollback()

# 8. 关闭连接（先关闭游标，再关闭连接）
cursor.close()
conn.close()